問題描述
PSQL 數據庫傳輸和錯誤 (PSQL database transfer and errors)
我正在將一個 Rails 應用程序轉移到一個新服務器上,並且遇到了一些錯誤,導致某些數據無法在具有相同數據庫架構的應用程序之間傳輸。
如果我在兩台服務器上運行 rake db:version ,我得到相同的結果。在我的新服務器上:
RAILS_ENV=production rake db:version # Returns 20181207224901
在我的舊服務器上:
heroku run rake db:version # Returns 20181207224901
我使用以下命令從舊服務器獲取數據:
pg_dump ‑‑host=OMITTED_HERE_FOR_PRIVACY_SAKE ‑‑port=5432 ‑‑username=OMITTED_HERE_FOR_PRIVACY_SAKE ‑‑password ‑‑dbname=da466m517q6qf6 ‑t product_order_rows > pox4_product_order_rows.pg
我知道這一點是正確的服務器,並檢查了 pg 文件的內容以確保它給了我想要的東西,我將在這篇文章中省略,因為它有太多行。
然後我嘗試放置我的新數據庫中的轉儲如下:
sudo psql ‑U pox4 pox4_production < pox4_product_order_rows.pg
我收到以下錯誤:
SET
SET
SET
SET
set_config
‑‑‑‑‑‑‑‑‑‑‑‑
(1 row)
SET
SET
SET
SET
SET
ERROR: relation "product_order_rows" already exists
ERROR: role "wsgdzocxqkyzmj" does not exist
ERROR: relation "product_order_rows_id_seq" already exists
ERROR: role "wsgdzocxqkyzmj" does not exist
ALTER SEQUENCE
ALTER TABLE
ERROR: insert or update on table "product_order_rows" violates foreign key constraint "fk_rails_7fc701b8a5"
DETAIL: Key (model_id)=(17176) is not present in table "models".
setval
‑‑‑‑‑‑‑‑
5021
(1 row)
ERROR: multiple primary keys for table "product_order_rows" are not allowed
ERROR: relation "index_product_order_rows_on_model_id" already exists
ERROR: relation "index_product_order_rows_on_product_order_id" already exists
ERROR: constraint "fk_rails_7fc701b8a5" for relation "product_order_rows" already exists
ERROR: constraint "fk_rails_d38880b40c" for relation "product_order_rows" already exists
檢查應放置數據的應用程序,我看不到任何應該傳輸的數據。我在其他表傳輸中遇到了一些錯誤,並且數據以某種方式進入了。我不明白的是,如果它們具有相同的模式,數據甚至可能首先出現在第一個數據庫中,或者為什麼它會在相同的數據庫中被拒絕。
參考解法
方法 1:
when you run heroku run rake db:version
you create the schema of your database. pg_dump
by default will dump the schema creation in your pox4_product_order_rows.pg what why you have an error.
You can add the ‑‑data‑only
option to pg_dump to dump the data without the schema. In your case:
pg_dump ‑‑host=OMITTED_HERE_FOR_PRIVACY_SAKE ‑‑port=5432 ‑‑username=OMITTED_HERE_FOR_PRIVACY_SAKE ‑‑password ‑‑dbname=da466m517q6qf6 ‑t product_order_rows ‑‑data‑only > pox4_product_order_rows.pg
You can also use pg_restore to only restore the data:
pg_retore ‑U pox4 ‑‑data‑only ‑‑dbname pox4_production pox4_product_order_rows.pg
(by Colin Brogan、kletord)